# 学生课程成绩信息的ORM模型

from datetime import  datetime
from enum import IntEnum
from sqlalchemy.orm import declarative_base,relationship,backref
from sqlalchemy import Column,Integer,String,SmallInteger,DateTime,Boolean,Enum,ForeignKey

# 如果sqlalchemy没有对应的类型 可以使用以下两种方式 导入类型
# 第一种方式导入CHAR
from sqlalchemy.types import CHAR
# 第二种方式导入CHAR
from sqlalchemy.dialects.mysql import TINYINT



# 第二步，声明ORM模型的基类
Base = declarative_base()


class SexEnum(IntEnum):
    男 = 1     #男
    女 = 2   #女

class Student(Base):
    # 学生信息表
    __tablename__ = 'school_student_info'
    id = Column(Integer,name='id',primary_key=True)
    stu_no = Column(Integer,nullable=False,unique=True,comment='学号')
    stu_name = Column(String(16), nullable=False, comment='姓名')
    sex = Column(Enum(SexEnum),comment='性别')
    age = Column(TINYINT(unsigned=True), default=0, comment='年龄',doc='年龄只能是正整数')
    class_name = Column(String(10), comment='班级')
    address = Column(String(255), comment='家庭住址')
    phone_no = Column(CHAR(11), comment='电话号码')


    # 重写__repr__方法,print() 打印Student的时候就会调用
    def __repr__(self):
        return '{}:{} '.format(self.stu_no,self.stu_name)

class Course(Base):
    # 课程信息表
    __tablename__ = 'school_course_info'
    id = Column(Integer, primary_key=True)
    course_name = Column(String(64), nullable=False)
    teacher = Column(String(16))
    desc = Column(String(512))

    def __repr__(self):
        return '课程:{} '.format(self.course_name)




class StudentGrade(Base):
    # 学生成绩表
    __tablename__ = 'school_student_grade'
    id = Column(Integer, primary_key=True)
    course_id = Column(Integer,ForeignKey(Course.id),nullable=False,comment='课程ID')
    # student_id = Column(Integer, ForeignKey(Student.id), nullable=False, comment='学生ID')
    student_id = Column(Integer, ForeignKey(Student.id,ondelete='CASCADE'), nullable=False, comment='学生ID')
    score = Column(SmallInteger, nullable=False,comment='成绩')
    created_at = Column(DateTime,comment='考试时间')

    # student = relationship(Student,backref='grade_list')
    student = relationship(Student,backref=backref('grade_list',cascade='all,delete'))
    course = relationship(Course, backref='grade_list')

    def __repr__(self):
        return '{}-{}:{} '.format(self.student.stu_name,self.course.course_name,self.score)


def create_table():
    # 创建表
    from db_engine import engine
    Base.metadata.create_all(engine)


def drop_table():
    # 删除表
    from db_engine import engine
    Base.metadata.drop_all(engine)